﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using ZLDC.Common.BaseUser;
using System.Data.SqlClient;

namespace ZLDC.DAL
{
    public class T_s_pdmDAL
    {
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static DataTable GetT_s_pdmParentMenu()
        {
            DataTable dt = new DataTable();
            string strSql = "select distinct(mdl) from kfb.zldc_t_s_pdm where isMenu=1 and IsActive=1 and mdl in('报表管理','人事管理')";
            
            dt=DbHelperSQL.Query(strSql).Tables[0];
            return dt;
        }

        /// <summary>
        /// 返回可操作权限
        /// </summary>
        /// <param name="EmpID"></param>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static DataTable GetPdmListByEmp(string EmpID, string strWhere)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@EmpID", SqlDbType.Int, 10),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
            parameters[0].Value = EmpID;
            parameters[1].Value = strWhere;
            return DbHelperSQL.RunProcedure("kfb.zldc_GetAllPdmByRoleIDS", parameters, "ds").Tables[0];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static DataTable GetT_s_pdmAllParentMenu(string strWhere)
        {
            DataTable dt = new DataTable();
            string strSql = "select distinct(mdl) from kfb.zldc_t_s_pdm ";
            if (strWhere.Length > 0)
                strSql += strWhere;

            dt = DbHelperSQL.Query(strSql).Tables[0];
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strMdl"></param>
        /// <returns></returns>
        public static DataTable GetT_s_pdmByMdl(string strMdl)
        {
            DataTable dt = new DataTable();

            string strSql = "select * from kfb.zldc_t_s_pdm where isMenu=1 and IsActive=1 and mdl='" + strMdl.Trim() + "' order by OrderNo ";
            dt = DbHelperSQL.Query(strSql).Tables[0];
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static DataTable GetT_s_pdmList(string strWhere)
        {
            string strSql = "select * from kfb.zldc_t_s_pdm ";
            if (strWhere.Trim() != "")
                strSql += " where " + strWhere;
            return DbHelperSQL.Query(strSql).Tables[0];
        }

         /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static DataTable GetPagerT_s_RoleList(string strWhere, int currentPage, int pagesize, out int recordcount)
        {
            recordcount = 0;

            int startRow = (currentPage - 1) * pagesize + 1;
            string strSql = " select count(id) as counts from kfb.zldc_t_s_role " + strWhere;
            strSql += " select * from (select (ROW_NUMBER() OVER (ORDER BY id asc )) AS Row ,* from kfb.zldc_t_s_role " + strWhere + ") as A";
            strSql += " where A.Row between " + startRow + " and " + currentPage * pagesize + "";

            DataSet ds = DbHelperSQL.Query(strSql);
            recordcount = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
            return ds.Tables[1];
        }

        /// <summary>
        /// 角色
        /// </summary>
        /// <param name="RoleID"></param>
        /// <param name="pdm"></param>
        public static void UpdateRolePdm(string RoleID, string pdm)
        {
            string strSql = "Update kfb.zldc_t_s_Role set Pdm='" + pdm + "' where ID=" + RoleID;
           DbHelperSQL.ExecuteSql(strSql);
        }

        /// <summary>
        /// 查询角色
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static DataTable GetRoleList(string strWhere)
        {
            string strSql = " select * from kfb.zldc_t_s_Role ";
            if (strWhere.Length > 0)
                strSql += strWhere;
            return DbHelperSQL.Query(strSql).Tables[0];
        }

        /// <summary>
        /// 插入角色
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="dsc"></param>
        public static void InsertRole(string Name, string dsc,string RoleCode,string pdm)
        {
            string strSql = " insert into kfb.zldc_t_s_Role(name,dsc,pdm,RoleCode) values('" + Name + "','" + dsc + "','" + pdm + "','" + RoleCode + "')";
            DbHelperSQL.ExecuteSql(strSql);
        }

        /// <summary>
        /// 更新角色
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="dsc"></param>
        /// <param name="RoleID"></param>
        public static void UpdateRole(string Name, string dsc,string RoleCode, string RoleID)
        {
            string strSql = " update kfb.zldc_t_s_Role set Name='" + Name + "',dsc='" + dsc + "', RoleCode='" + RoleCode + "' where id=" + RoleID;
            DbHelperSQL.ExecuteSql(strSql);
        }

        /// <summary>
        /// 删除角色
        /// </summary>
        /// <param name="RoleID"></param>
        public static void DeleteRole(string RoleID)
        {
            string strSql = " delete from kfb.zldc_t_s_Role where id=" + RoleID;
            DbHelperSQL.ExecuteSql(strSql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="RoleID"></param>
        public static void DeleteT_s_Role_EmployeeByRoleID(string RoleID)
        {
            string strSql = " delete from kfb.zldc_t_s_Role_Employee where RoleID=" + RoleID;
            DbHelperSQL.ExecuteSql(strSql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="RoleID"></param>
        /// <param name="EmpID"></param>
        public static void DeleteT_s_Role_EmployeeByRoleIDandEmpID(string RoleID,string EmpID)
        {
            string strSql = " delete from kfb.zldc_t_s_Role_Employee where RoleID=" + RoleID + " and EmpID=" + EmpID;
            DbHelperSQL.ExecuteSql(strSql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="RoleID"></param>
        /// <param name="EmpID"></param>
        public static void DeleteT_s_Employee_DeptByEmpIDandDeptID(string EmpID, string DeptID)
        {
            string strSql = " delete from zldc.t_s_Employee_Dept where EmpID='" + EmpID + "' and DeptID='" + DeptID+"'";
            DbHelperSQL.ExecuteSql(strSql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="RoleID"></param>
        /// <param name="EmpID"></param>
        public static void InsertT_s_Role_Employee(string RoleID, string EmpID)
        {
            string strSql=" Insert into kfb.zldc_t_s_Role_Employee(RoleID,EmpID) values('"+RoleID+"','"+EmpID+"')";
            DbHelperSQL.ExecuteSql(strSql);
        }

        public static void CopyRole(string RoleID)
        {
            string strSql = "insert into zldc_t_s_role(name,dsc,pdm) " +
                          "select name,dsc,pdm from t_s_role where Id=" + RoleID;
            DbHelperSQL.ExecuteSql(strSql);
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static DataTable GetOrg_UnitList(string strWhere, int currentPage, int pagesize, out int recordcount)
        {
            recordcount = 0;

            int startRow = (currentPage - 1) * pagesize + 1;
            string strSql = " select count(*) as counts from org_unit " + strWhere;
            DataBaseAll db = new DataBaseAll("ZLDCHR");
            DataSet ds = db.SelectDataBaseAll(strSql, "A");
            if (ds.Tables["A"].Rows.Count > 0)
                recordcount = Convert.ToInt32(ds.Tables["A"].Rows[0][0]);

            strSql = "";
            strSql += " select * from (select (ROW_NUMBER() OVER (ORDER BY id asc )) AS Row ,* from org_unit " + strWhere + ") as A";
            strSql += " where A.Row between " + startRow + " and " + currentPage * pagesize + "";

            DataSet dss = db.SelectDataBaseAll(strSql, "org");
            return dss.Tables["org"];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public static DataTable GetT_s_Employee_DeptList(string empID)
        {
            string strSql = "  select * from zldc_t_s_dept where OrgID in( select DeptID from zldc.t_s_Employee_Dept  where EmpID='" + empID + "') ";
            return DbHelperSQL.Query(strSql).Tables[0];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="RoleID"></param>
        /// <param name="EmpID"></param>
        public static void InsertT_s_Employee_Dept(string EmpID, string DeptID,string strType)
        {
            string strSql = " Insert into zldc.T_s_Employee_Dept(EmpID,DeptID,ModelStr) values('" + EmpID + "','" + DeptID + "','"+strType+"')";
            DbHelperSQL.ExecuteSql(strSql);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="Mdl"></param>
        /// <returns></returns>
        public static DataTable GetMenuByMdl(string Mdl)
        {
            string strSql = "select distinct menu from zldc_t_s_pdm where mdl='" + Mdl + "' and isMenu=1 and IsActive=1 ";
            return DbHelperSQL.Query(strSql).Tables[0];
        }

          /// <summary>
        /// 
        /// </summary>
        /// <param name="Mdl"></param>
        /// <returns></returns>
        public static DataTable GetPdmByMenu(string Menu)
        {
            string strSql = "select * from zldc_t_s_pdm where menu='"+Menu+"' and isMenu=1 and IsActive=1 order by OrderNo  ";
            return DbHelperSQL.Query(strSql).Tables[0];
        }
    }
}
